-- chen_yi 低代码平台
-- 模板生成sql
-- @date ${.now?string("yyyy-MM-dd HH:mm:ss")}
-- 表名 ${name}
<#include "common.ftl">

SET NAMES ${charset?upper_case};

DROP TABLE IF EXISTS ${sanitize(name)};
CREATE TABLE ${sanitize(name)}(
<#list (fields) as field>
    ${'\t'}`${sanitize(field.fieldName)}` ${field.fieldType?upper_case}<#t>
<#-- 字段长度以及小数点 -->
    <#if field.fieldLength??>(${field.fieldLength?number?c}<#if field.fieldDecimal??>,${field.fieldDecimal?number?c}</#if>)</#if><#t>
<#-- 是否区分大小写 -->
    <#if collate??&&(collate=="Y" && (field.fieldType=="varchar"||field.fieldType=="char"||field.fieldType=="text"||field.fieldType=="long text"||field.fieldType=="enum"))> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs <#elseif collate??&&(collate=="N" && (field.fieldType=="varchar"||field.fieldType=="char"||field.fieldType=="text"||field.fieldType=="long text"||field.fieldType=="enum"))> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci </#if><#t>
<#-- 是否无符号 -->
    <#if field.unsignedFlag??&&field.unsignedFlag=="Y"> UNSIGNED </#if><#t>
<#-- 是否填充0 -->
    <#if field.fillZeroFlag??&&field.fillZeroFlag=="Y"> ZEROFILL </#if><#t>
<#-- 虚拟列类型判断 -->
    <#if field.virtualFlag??&&field.virtualFlag=="Y">
        GENERATED ALWAYS AS (${sanitize(field.virtualExpression)}) ${field.virtualType} <#t>
    </#if>
<#-- 是否允许为空 -->
    <#if (field.notNullFlag??&&field.notNullFlag=="Y") || (field.primaryFlag??&&field.primaryFlag=="Y") ||(field.autoIncrementFlag??&&field.autoIncrementFlag=="Y")> NOT NULL <#else > NULL</#if><#t>
<#-- 默认值 -->
    <#if field.defaultValue??> DEFAULT ${field.defaultValue}</#if><#t>
<#-- 是否为主键 -->
    <#if field.primaryFlag??&&field.primaryFlag=="Y"> PRIMARY KEY </#if><#t>
<#-- 是否自增 -->
    <#if field.autoIncrementFlag??&&field.autoIncrementFlag=="Y"> AUTO_INCREMENT </#if><#t>
<#-- 注释 -->
    <#if field.comment??> COMMENT '${sanitize(field.comment)}'</#if><#if field_has_next||(indexes?? && indexes?size gt 0)><#t>,</#if>
</#list>
<#-- 创建索引 -->
<#if indexes?? && indexes?size gt 0>
    <#list indexes as index>
        <#if index.indexType == "FULLTEXT INDEX" || index.indexType == "SPATIAL INDEX">
            ${'\t'}${index.indexType} `${sanitize(index.indexName)}` (${index.fieldName})<#t>
        <#elseif index.indexType == "INVISIBLE INDEX">
            ${'\t'}INDEX `${sanitize(index.indexName)}` (${index.fieldName}) INVISIBLE<#t>
        <#else>
            ${'\t'}<#if index.indexType == "UNIQUE INDEX">UNIQUE </#if>INDEX `${sanitize(index.indexName)}` (${index.fieldName})<#t>
        </#if>
        <#if index.indexFunction?? && index.indexType != "PRIMARY KEY"> USING ${index.indexFunction}</#if><#t>
        <#if index.comment??> COMMENT '${sanitize(index.comment)}'</#if><#t>
        <#if index_has_next ||( foreignKeys?? && foreignKeys?size gt 0)>,<#t></#if>
    </#list>
</#if>
<#-- 创建外键 -->
<#if foreignKeys?? && foreignKeys?size gt 0>
    <#list foreignKeys as fk>
        ${'\t'}CONSTRAINT `${sanitize(fk.foreignKeyName)}` FOREIGN KEY (${sanitize(fk.fields)})<#t>
        REFERENCES ${sanitize(fk.referenceTableName)} (${sanitize(fk.referenceFieldName)})<#t>
        <#if fk.whenDelete??> ON DELETE ${fk.whenDelete}</#if><#t>
        <#if fk.whenUpdate??> ON UPDATE ${fk.whenUpdate}</#if><#t>
        <#if fk_has_next>,<#t></#if>
    </#list>
</#if>
) ENGINE = ${engine?upper_case} CHARACTER SET = ${charset?upper_case} COLLATE = <#if collate=="Y">utf8mb4_0900_as_cs<#else >utf8mb4_0900_ai_ci</#if> <#if comment?has_content> COMMENT = '${sanitize(comment)}'</#if> ROW_FORMAT = DYNAMIC;
